<?php
/**
 * Created by 柚子i.
 * User: Emperor
 * Date: 2019/6/12
 * Time: 15:15
 */

namespace app\common\service\gis;


use app\common\service\BaseService;
use think\Db;

class GisStation extends BaseService
{
    /**
     * Notes :组合站点数据
     * @return array
     * @throws \Exception
     * @author lichao   2019/6/12
     */
    public function mergeStationData()
    {
        try {
            $stationDataAll = array();
            for ($i = 1; $i < 6; $i++) {
                // 获取站点信息
                $stationSql  = "SELECT * FROM `basic_station` WHERE `region_id` = 53 AND `station_id` = ?";
                $stationInfo = Db::query($stationSql, array($i));
                // if (!$stationInfo) {
                //     throw new \Exception('获取站点信息失败！');
                // }

                // 获取站点污染物 6参
                $currentTime         = date("Y-m-d 00:00:00");
                $stationForecastSql  = "SELECT * FROM `air_quality_station_aqi_forecast_day` WHERE `region_id` = 53 AND 
`station_id` = ? AND `predict_time` = ? AND `publish_time` = ?";
                $stationForecastData = Db::query($stationForecastSql, array($i, $currentTime, $currentTime));
                // if (!$stationForecastData) {
                //     throw new \Exception('获取站点预测值失败！');
                // }

                $stationData = [
                    "type"       => "Feature",
                    "properties" => [
                        "id"      => $stationInfo[0]['station_id'],         // 站点id
                        "name"    => $stationInfo[0]['name'],    // 站点名称
                        "tooltip" => [
                            "aqi"  => isset($stationForecastData[0]) ? round($stationForecastData[0]['aqi']) : '',
                            "pm25" => isset($stationForecastData[0]) ? round($stationForecastData[0]['pm25']) : '',
                            "pm10" => isset($stationForecastData[0]) ? round($stationForecastData[0]['pm10']) : '',
                            "so2"  => isset($stationForecastData[0]) ? round($stationForecastData[0]['so2']) : '',
                            "no2"  => isset($stationForecastData[0]) ? round($stationForecastData[0]['no2']) : '',
                            "co"   => isset($stationForecastData[0]) ? round($stationForecastData[0]['co'], 1) : '',
                            "o3"   => isset($stationForecastData[0]) ? round($stationForecastData[0]['o3']) : '',
                        ],
                    ]
                ];

                $stationData['geometry'] = [
                    "type"        => "Point",
                    "coordinates" => [$stationInfo[0]['longitude'], $stationInfo[0]['latitude']] //经纬度
                ];

                array_push($stationDataAll, $stationData);
            }

            return $stationDataAll;
        } catch (\Exception $e) {
            throw $e;
        }
    }

    /**
     * Notes :获取5个站点14天数据
     * @param $param
     * @return array
     * @throws \Exception
     * @author lichao   2019/6/13
     */
    public function getStationData($param)
    {
        try {
            $dateArr      = [];
            $pollutantArr = [];
            for ($i = 1; $i < 6; $i++) {
                // 站点实测
                $sevenDaysAgo        = date("Y-m-d 00:00:00", strtotime("-7 day"));
                $beforeDay           = date("Y-m-d 00:00:00", strtotime("-1 day"));
                $stationRealtimeSql  = "SELECT * FROM `air_quality_station_aqi_realtime_day` WHERE `region_id` = 53 AND 
`station_id` = ? AND `publish_time` between ? AND ?";
                $stationRealtimeData = Db::query($stationRealtimeSql, array($i, $sevenDaysAgo, $beforeDay));
                // if (!$stationRealtimeData) {
                //     throw new \Exception('获取站点实际值失败！');
                // }
                // echo "站点实测数据----------";
                // dump($stationRealtimeData);

                // 处理站点实测数据
                $realtimeDate      = array_column($stationRealtimeData, 'publish_time');
                $realtimePollutant = array_column($stationRealtimeData, $param);

                // 站点预报
                $currentTime         = date("Y-m-d 00:00:00");
                $stationForecastSql  = "SELECT * FROM `air_quality_station_aqi_forecast_day` WHERE `region_id` = 53 AND 
`station_id` = ? AND `publish_time` = ?";
                $stationForecastData = Db::query($stationForecastSql, array($i, $currentTime));
                if (!$stationForecastData) {
                    throw new \Exception('获取站点预测值失败！');
                }
                // echo "站点预报数据----------";
                // dump($stationForecastData);

                // 处理站点预报值
                $forecastDate      = array_column($stationForecastData, 'predict_time');
                $forecastPollutant = array_column($stationForecastData, $param);

                // 组合数据
                $dateArr   = handleStationTime(array_merge_recursive($realtimeDate, $forecastDate));
                $pollutant = roundStationData(array_merge_recursive($realtimePollutant, $forecastPollutant), $param);

                array_push($pollutantArr, $pollutant);
            }

            // 整合数组
            $stationArr = [
                "date" => $dateArr,
                "data" => $pollutantArr
            ];

            return $stationArr;
        } catch (\Exception $e) {
            throw $e;
        }
    }
    
    /**
     * 获取企业相关数据及坐标
     * @author Naomi
     * @param $where
     * @return array
     */
    public function getCompanyData($where)
    {
        $dayDate = date('Y-m-d', strtotime("-1 day"));
        $hourDate = date('G:00:00', strtotime("-1 Hour"));
        
    
        //获取天数据
        $workingDayData = $this->getWorkingDay($dayDate);
        //获取时数据
        $workingHourData = $this->getWorkingHour($hourDate);
        
        //企业数据
        $companySql = "SELECT id,name,short_name,longitude,latitude FROM `basic_company` $where";
        $companyData = Db::query($companySql);
        
        
        
        //整合数据
        $data = [];
        foreach ($companyData as $key => $value) {
            $data[$key] = [
                "type" => "Feature",
                "properties" => [
                    "id" => $value['id'],
                    "name" => $value['name'],
                    "short_name" => $value['short_name'],
                    "tooltip" => [
                        "emissions" => [],
                        "operation" => []
                    ]
                ],
                "geometry" => [
                    "type" => "Point",
                    "coordinates" => [
                        $value['longitude'],
                        $value['latitude']
                    ]
                ]
            ];
            foreach ($workingDayData as $k => $v) {
                if ($v['company_id'] === $data[$key]['properties']['id']) {
                    $data[$key]['properties']['tooltip']['emissions'][] = [
                        "drain_name" => $v['drain_name'],
                        "so2" => round($v['so2'] / pow(10, 9), 2),
                        "nox" => round($v['nox'] / pow(10, 9), 2),
                    ];
                }
            }
    
            foreach ($workingHourData as $k => $v) {
                if ($v['company_id'] === $data[$key]['properties']['id']) {
                    $data[$key]['properties']['tooltip']['operation'][] = [
                        "drain_name" => $v['drain_name'],
                        "operation_concentration_so2" => round($v['operation_concentration_so2'], 1),
                        "operating_efficiency_so2" => round($v['operating_efficiency_so2'] * 100, 1),
                        "operation_concentration_nox" => round($v['operation_concentration_nox'], 1),
                        "operating_efficiency_nox" => round($v['operating_efficiency_nox'] * 100, 1),
                    ];
                }
            }
        }
        return $data;
    }
    
    /**
     * 获取工况天数据
     * @author Naomi
     * @param $date
     * @return array
     */
    public function getWorkingDay($date)
    {
        //模拟数据
        $simulationSql = "SELECT company_id,drain_name,emissions_so2_total as so2,emissions_nox_total as nox FROM `company_industry_monitor_simulation_day_avg`";
        $simulationData = Db::query($simulationSql);
        //实时数据
        $sulfurSql = "SELECT c.publish_time,c.company_id,c.drain_id,c.drain_name,sum(c.emissions) as so2 FROM `company_industry_monitor_sulfur_day` c WHERE c.publish_time = '{$date}' AND c.power = 1 GROUP BY `company_id`,`publish_time`,`drain_id`";
        $nitreSql = "SELECT publish_time,company_id,drain_id,drain_name,sum(emissions) as nox FROM `company_industry_monitor_nitre_day` WHERE publish_time = '{$date}' AND power = 1 GROUP BY `company_id`,`publish_time`,`drain_id`";
        $reaSql = "SELECT a.company_id,a.drain_name,a.so2,b.nox FROM ({$sulfurSql}) a LEFT JOIN ({$nitreSql}) b ON a.publish_time=b.publish_time and a.company_id = b.company_id AND a.drain_id=b.drain_id";
        $reaData = Db::query($reaSql);

        return array_merge($simulationData, $reaData);
    }
    
    /**
     * 获取工况时数据
     * @author Naomi
     * @param $date
     * @return array
     */
    public function getWorkingHour($date)
    {
        $hourDate = date('Y-m-d H:00:00', strtotime($date));
        //模拟数据
        $simulationSql = "SELECT company_id,drain_name,operation_concentration_so2,operation_concentration_nox,operating_efficiency_so2 as operating_efficiency_so2,operating_efficiency_nox as operating_efficiency_nox FROM `company_industry_monitor_simulation_hour_avg` WHERE publish_hour = '{$date}'";
        $simulationData = Db::query($simulationSql);
        //实时数据
        $sulfurSql = "SELECT c.publish_time,c.company_id,c.drain_id,c.drain_name,sum(c.emissions) / sum(c.emissions / c.operation_concentration) as operation_concentration_so2,1-(sum(c.emissions) / sum(c.emissions / (1-c.operating_efficiency))) as operating_efficiency_so2 FROM `company_industry_monitor_sulfur_hour` c WHERE c.publish_time = '{$hourDate}' AND (c.power = 1 OR c.power = -1) GROUP BY `company_id`,`publish_time`,`drain_id`";
        $nitreSql = "SELECT publish_time,company_id,drain_id,drain_name,sum(emissions) / sum(emissions / operation_concentration) as operation_concentration_nox,1-(sum(emissions) / sum(emissions / (1-operating_efficiency))) as operating_efficiency_nox FROM `company_industry_monitor_nitre_hour` WHERE publish_time = '{$hourDate}' AND (power = 1 OR power = -1) GROUP BY `company_id`,`publish_time`,`drain_id`";
        $reaSql = "SELECT a.company_id,a.drain_name,a.operation_concentration_so2,a.operating_efficiency_so2,b.operation_concentration_nox,b.operating_efficiency_nox FROM ({$sulfurSql}) a LEFT JOIN ({$nitreSql}) b ON a.publish_time=b.publish_time and a.company_id = b.company_id AND a.drain_id=b.drain_id";
        $reaData = Db::query($reaSql);
        return array_merge($simulationData, $reaData);
    }
}